<?php

namespace app\utils;

use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Writer\Csv;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class Excel
{
    /** @var array 表头映射 */
    private $headerMap = [];
    
    /** @var array 反向映射规则 */
    private $reverseMappings = [];
    
    /** @var string 子级字段名 */
    private $childrenKey = '';
    
    /** @var array 每层显示的字段 */
    private $levelFields = [];
    
    /** @var array 第一层表头 */
    private $firstLevelHeaders = [];
    
    /** @var array 其他层表头 */
    private $otherLevelHeaders = [];

    /** @var bool 是否自动调整列宽 */
    private $autoSizeColumns = true;

    /**
     * 获取是否自动调整列宽
     */
    public function getAutoSizeColumns(): bool
    {
        return $this->autoSizeColumns;
    }

    /**
     * 设置是否自动调整列宽
     */
    public function setAutoSizeColumns(bool $autoSizeColumns): self
    {
        $this->autoSizeColumns = $autoSizeColumns;
        return $this;
    }


    /**
     * 设置表头映射
     */
    public function setHeaderMap(array $headerMap): self
    {
        $this->headerMap = $headerMap;
        return $this;
    }

    /**
     * 设置子级字段名
     */
    public function setChildrenKey(string $childrenKey): self
    {
        $this->childrenKey = $childrenKey;
        return $this;
    }

    /**
     * 设置每层显示的字段
     */
    public function setLevelFields(array $levelFields): self
    {
        $this->levelFields = $levelFields;
        return $this;
    }

    /**
     * 设置第一层表头
     */
    public function setFirstLevelHeaders(array $headers): self
    {
        $this->firstLevelHeaders = $headers;
        return $this;
    }

    /**
     * 设置其他层表头
     */
    public function setOtherLevelHeaders(array $headers): self
    {
        $this->otherLevelHeaders = $headers;
        return $this;
    }

    /**
     * 获取表头映射
     */
    public function getHeaderMap(): array
    {
        return $this->headerMap;
    }

    /**
     * 设置反向映射规则
     */
    public function setReverseMappings(array $reverseMappings): self
    {
        $this->reverseMappings = $reverseMappings;
        return $this;
    }

    /**
     * 获取反向映射规则
     */
    public function getReverseMappings(): array
    {
        return $this->reverseMappings;
    }


    /**
     * 导出数据到Excel或CSV
     * @param array $data 数据
     * @param string $filename 文件名
     * @param string $format 导出格式，支持 'xlsx' 和 'csv'
     * @return mixed
     */
    public function export(array $data, string $filename = '', string $format = 'xlsx')
    {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        // 设置表头
        $headers = array_values($this->headerMap);
        foreach ($headers as $index => $header) {
            $column = Coordinate::stringFromColumnIndex($index + 1);
            $sheet->setCellValue($column . '1', $header);
        }

        // 填充数据
        $row = 2;
        foreach ($data as $item) {
            $col = 1;
            foreach ($this->headerMap as $dbField => $displayName) {
                $value = $item[$dbField] ?? '';

                // 反向映射处理
                if (isset($this->reverseMappings[$dbField])) {
                    $value = $this->reverseMappings[$dbField][$value] ?? $value;
                }

                $column = Coordinate::stringFromColumnIndex($col);
                $sheet->setCellValue($column . $row, $value);
                $col++;
            }
            $row++;
        }

        // 设置表头样式
        $headerStyle = [
            'font' => ['bold' => true],
            'fill' => [
                'fillType' => Fill::FILL_SOLID,
                'color' => ['rgb' => 'E6E6FA']
            ]
        ];

        $lastColumn = $sheet->getHighestColumn();
        $sheet->getStyle('A1:' . $lastColumn . '1')->applyFromArray($headerStyle);

        // 自动调整列宽
        foreach (range('A', $lastColumn) as $column) {
            $sheet->getColumnDimension($column)->setAutoSize(true);
        }

        // 根据格式选择写入器
        $writer = $this->getWriterByFormat($spreadsheet, $format);
        $filePath = File::getTemplatePath($format);
        $writer->save($filePath);

        // 生成文件名
        $encodedName = urlencode($filename);
        $fileName = date("Ymd_His") . "-{$encodedName}.{$format}";

        return download($filePath, $fileName);
    }

    /**
     * 根据格式获取对应的写入器
     * @param Spreadsheet $spreadsheet
     * @param string $format
     * @return \PhpOffice\PhpSpreadsheet\Writer\IWriter
     * @throws \InvalidArgumentException
     */
    protected function getWriterByFormat(Spreadsheet $spreadsheet, string $format)
    {
        switch (strtolower($format)) {
            case 'xlsx':
                return new Xlsx($spreadsheet);
            case 'csv':
                return new Csv($spreadsheet);
            default:
                throw new \InvalidArgumentException("不支持的导出格式: {$format}");
        }
    }


    /**
     * 读取Excel文件
     * @param $filePath
     * @return mixed
     */
    public static function extractExcelArray($filePath)
    {
        $spreadsheet = IOFactory::load($filePath);
        return $spreadsheet->getActiveSheet();
    }

    /**
     * 获取Excel列
     * @param $filePath
     * @param $title
     * @return array
     */
    public static function extractExcelColumns($filePath, $title)
    {
        $list = self::extractExcelArray($filePath);
        $titles = array_shift($list);
        $index = array_search($title, $titles);

        if ($index === false) {
            return [];
        }
        return array_map(function ($item) use ($index) {
            return $item[$index] ?? '';
        }, $list);
    }

    /**
     * 获取Excel表头
     * @param $excelData
     * @return array
     */
    public static function getHeader($excelData)
    {
        $header = [];
        foreach ($excelData->getRowIterator(1, 1) as $row) {
            foreach ($row->getCellIterator() as $cell) {
                $header[] = $cell->getValue();
            }
        }
        return $header;
    }

    /**
     * 获取Excel数据
     * @param $excelData
     * @return array
     */
    public static function getData($excelData)
    {
        $data = [];
        foreach ($excelData->getRowIterator(2) as $row) {
            $rowData = [];
            foreach ($row->getCellIterator() as $cell) {
                $rowData[] = $cell->getValue();
            }
            $data[] = $rowData;
        }
        return $data;
    }


    /**
     * 通用分层级数据导出到Excel
     * @param array $hierarchicalData 层级数据
     * @param string $filename 文件名
     * @param string $format 导出格式
     * @return mixed
     */
    public function exportHierarchicalData(array $hierarchicalData, string $filename = 'hierarchical_export', string $format = 'xlsx')
    {
        // 检测最大层数并动态生成表头
        $maxDepth = $this->getMaxDepth($hierarchicalData);
        $this->buildDynamicHeaders($maxDepth);

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        // 设置表头
        $this->setHeaders($sheet);

        // 导出数据
        $currentRow = $this->exportData($sheet, $hierarchicalData);

        // 应用样式
        $this->applyStyles($sheet, $currentRow);

        // 保存并下载文件
        return $this->saveAndDownload($spreadsheet, $filename, $format);
    }

    /**
     * 检测数据最大层数
     * @param array $data 数据
     * @param int $currentDepth 当前层数
     * @return int
     */
    protected function getMaxDepth(array $data, int $currentDepth = 1): int
    {
        $maxDepth = $currentDepth;

        foreach ($data as $item) {
            $children = $item[$this->childrenKey] ?? [];
            if (!empty($children)) {
                $childDepth = $this->getMaxDepth($children, $currentDepth + 1);
                $maxDepth = max($maxDepth, $childDepth);
            }
        }

        return $maxDepth;
    }

    /**
     * 动态生成表头
     * @param int $maxDepth 最大层数
     */
    protected function buildDynamicHeaders(int $maxDepth)
    {
        $this->headerMap = [];

        for ($level = 0; $level < $maxDepth; $level++) {
            // 第一层使用 firstLevelHeaders，其他层使用 otherLevelHeaders
            $headers = ($level === 0) ? $this->firstLevelHeaders : $this->otherLevelHeaders;
            
            foreach ($this->levelFields as $index => $field) {
                $key = "level{$level}_field{$index}";
                $header = $headers[$index] ?? $field;
                $this->headerMap[$key] = $header;
            }
        }
    }

    /**
     * 设置表头
     * @param Worksheet $sheet
     */
    protected function setHeaders(Worksheet $sheet)
    {
        $columnIndex = 1;
        foreach (array_values($this->headerMap) as $headerText) {
            $cellCoordinate = Coordinate::stringFromColumnIndex($columnIndex) . '1';
            $sheet->setCellValue($cellCoordinate, $headerText);
            $columnIndex++;
        }

        // 应用表头样式
        $lastColumn = Coordinate::stringFromColumnIndex($columnIndex - 1);
        $headerRange = 'A1:' . $lastColumn . '1';

        $sheet->getStyle($headerRange)->applyFromArray([
            'font' => ['bold' => true],
            'fill' => [
                'fillType' => Fill::FILL_SOLID,
                'color' => ['rgb' => 'E6E6FA']
            ],
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['rgb' => '000000']
                ]
            ]
        ]);
    }

    /**
     * 导出数据（递归写入）
     * @param Worksheet $sheet 工作表
     * @param array $data 数据
     * @param int $currentRow 当前行号
     * @param int $currentLevel 当前层级
     * @return int 最后行号
     */
    protected function exportData(Worksheet $sheet, array $data, int $currentRow = 2, int $currentLevel = 0)
    {
        foreach ($data as $item) {
            $currentRow = $this->writeItemRecursive($sheet, $item, $currentRow, 0, []);
        }
        return $currentRow;
    }

    /**
     * 递归写入节点（无限层级）
     * @param Worksheet $sheet 工作表
     * @param array $item 当前节点数据
     * @param int $currentRow 当前行号
     * @param int $level 当前层级
     * @param array $ancestors 祖先节点数组
     * @return int 最后行号
     */
    protected function writeItemRecursive(Worksheet $sheet, array $item, int $currentRow, int $level, array $ancestors)
    {
        $children = $item[$this->childrenKey] ?? [];
        $currentAncestors = array_merge($ancestors, [$item]);
        
        if (!empty($children)) {
            // 有子节点，递归处理
            $startRow = $currentRow;
            foreach ($children as $child) {
                $currentRow = $this->writeItemRecursive($sheet, $child, $currentRow, $level + 1, $currentAncestors);
            }
            // 合并当前层的单元格
            $this->mergeLevelCells($sheet, $level, $startRow, $currentRow - 1);
        } else {
            // 叶子节点，写入完整一行
            $this->writeFullRow($sheet, $currentAncestors, $currentRow);
            $currentRow++;
        }
        
        return $currentRow;
    }

    /**
     * 写入完整一行（包括所有祖先节点）
     * @param Worksheet $sheet 工作表
     * @param array $ancestors 祖先节点数组
     * @param int $row 行号
     */
    protected function writeFullRow(Worksheet $sheet, array $ancestors, int $row)
    {
        $columnIndex = 1;

        foreach ($ancestors as $level => $ancestor) {
            foreach ($this->levelFields as $field) {
                $value = $ancestor[$field] ?? '';
                $column = Coordinate::stringFromColumnIndex($columnIndex);
                $sheet->setCellValue($column . $row, $value);
                $sheet->getStyle($column . $row)
                    ->getAlignment()
                    ->setVertical(Alignment::VERTICAL_CENTER)
                    ->setHorizontal(Alignment::HORIZONTAL_CENTER);
                $columnIndex++;
            }
        }
    }

    /**
     * 合并层级单元格
     * @param Worksheet $sheet 工作表
     * @param int $level 层级
     * @param int $startRow 开始行
     * @param int $endRow 结束行
     */
    protected function mergeLevelCells(Worksheet $sheet, int $level, int $startRow, int $endRow)
    {
        if ($startRow >= $endRow) {
            return;
        }

        $startColumn = $level * count($this->levelFields) + 1;

        for ($i = 0; $i < count($this->levelFields); $i++) {
            $column = Coordinate::stringFromColumnIndex($startColumn + $i);
            $sheet->mergeCells("{$column}{$startRow}:{$column}{$endRow}")
                ->getStyle("{$column}{$startRow}")
                ->getAlignment()
                ->setVertical(Alignment::VERTICAL_CENTER)
                ->setHorizontal(Alignment::HORIZONTAL_CENTER);
        }
    }

    /**
     * 应用样式
     * @param Worksheet $sheet 工作表
     * @param int $lastRow 最后一行
     */
    protected function applyStyles(Worksheet $sheet, int $lastRow)
    {
        if ($this->autoSizeColumns) {
            $lastColumn = Coordinate::stringFromColumnIndex(count($this->headerMap));
            foreach (range('A', $lastColumn) as $column) {
                $sheet->getColumnDimension($column)->setAutoSize(true);
            }
        }

        // 添加边框
        if ($lastRow > 1) {
            $lastColumn = $sheet->getHighestColumn();
            $dataRange = 'A2:' . $lastColumn . $lastRow;
            $sheet->getStyle($dataRange)->applyFromArray([
                'borders' => [
                    'allBorders' => [
                        'borderStyle' => Border::BORDER_THIN,
                        'color' => ['rgb' => 'DDDDDD']
                    ]
                ]
            ]);
        }
    }

    /**
     * 保存并下载文件
     * @param Spreadsheet $spreadsheet 电子表格对象
     * @param string $filename 文件名
     * @param string $format 格式
     * @return mixed
     */
    protected function saveAndDownload(Spreadsheet $spreadsheet, string $filename, string $format)
    {
        $writer = $this->getWriterByFormat($spreadsheet, $format);
        $filePath = File::getTemplatePath($format);
        $writer->save($filePath);

        $encodedName = urlencode($filename);
        $fileName = date("Ymd_His") . "-{$encodedName}.{$format}";

        return download($filePath, $fileName);
    }


}